
package DataAccess;

import DomainModel.Course;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/*
 * @author Patrick
 */
public class CourseDA
{
    private Connection conn;
    private PreparedStatement prep;

    public CourseDA() throws Exception {
        conn = DBManager.getInstance().getConnection();
    }
    
    public void setCourse(Course course) throws SQLException {
        String q;
        
        if (course.getCourseId() == 0) {
            // INSERT new course
            q = "INSERT INTO courses (code, name, credit_hours) " +
                "VALUES (?, ?, ?)";
            prep = conn.prepareStatement(q);
            prep.setString(1, course.getCourseCode());
            prep.setString(2, course.getCourseName());
            prep.setInt(3, course.getCreditHours());
        }
        else {
            // UPDATE course
             q = "UPDATE courses " +
                 "SET code = ?, name =?, credit_hours = ? WHERE id = ?";
            prep = conn.prepareStatement(q);
            prep.setString(1, course.getCourseCode());
            prep.setString(2, course.getCourseName());
            prep.setInt(3, course.getCreditHours());
            prep.setInt(4, course.getCourseId());
        }
        
        try  {
            prep.executeUpdate();
        }
        catch (SQLException e) {
            throw e;
        }
        finally {
            prep.close();
        }
    }
    
    private Course createCourse(ResultSet rs) throws SQLException
    {
       if (rs == null) return null;
         
        int course_id = rs.getInt("id");
        String course_code = rs.getString("code");
        String course_name= rs.getString("name");
        int course_hours = rs.getInt("credit_hours");
        
        return new Course(course_id, course_code, course_name, course_hours);
    }
    
    public List<Course> getAllCourses() throws Exception {
       List<Course> allCourses = new LinkedList<>();
   
        String q = "SELECT * FROM courses";
        prep = conn.prepareStatement(q);
        
        try (ResultSet rs = prep.executeQuery()) {
            while (rs.next()) {
                allCourses.add(this.createCourse(rs));
            }
        }
        catch (Exception e) {
            System.out.println("Error while fetching courses list: "+e.getMessage());
            throw e;
        }
        finally {
            prep.close();
        }
        
        return allCourses; 
        
    }

     Course getCourseById(int course_id) throws Exception {
        Course course = null;
        String q = "SELECT * FROM courses WHERE id = ?";
        prep = conn.prepareStatement(q);
        prep.setInt(1, course_id);
        
        ResultSet rs = prep.executeQuery();       
   
        try {
            if(rs.next()) { //if the user was found
                if(course_id == rs.getInt("id"))
                    course = (this.createCourse(rs));  
            }
        }
        catch (Exception e) {
            System.out.println("getCourseById: Error while creating course: "+e.getMessage());
            throw e;
        }
        finally {
            prep.close();
        }
        return course;
    }

    boolean deleteCourse(int courseId) throws Exception {
        Course course = null;
       
        String query = "delete c from courses c where c.id = ?";
        prep = conn.prepareStatement(query);
        prep.setInt(1, courseId);
        
        try {
            prep.executeUpdate();
            course = getCourseById(courseId);
        } catch (SQLException e) {
            throw e;
        } finally {
            prep.close();
        }
        return (course == null);
    }
}
